package net.yla.smartbus.module.controller.pub.jdbc;

import com.alibaba.fastjson.JSONObject;
import net.yla.smartbus.core.api.ApiResult;
import net.yla.smartbus.core.api.RequestCode;
import net.yla.smartbus.core.base.BaseController;
import net.yla.smartbus.core.base.PageBean;
import net.yla.smartbus.core.constants.JdbcTypeEnum;
import net.yla.smartbus.core.exception.AppException;
import net.yla.smartbus.module.form.JdbcRequestForm;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.validation.annotation.Validated;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.sql.*;
import java.util.*;

/**
 * @author KongXianDe
 * @date 2019/7/27 9:40
 */
@RestController
@RequestMapping("/jdbc")
public class JDBCController extends BaseController {


    @Value("${spring.datasource.url}")
    private String url;
    @Value("${spring.datasource.username}")
    private String username;
    @Value("${spring.datasource.password}")
    private String password;
    @Value("${spring.datasource.driver-class-name}")
    private String driver;

    protected static final Logger logger = LoggerFactory.getLogger(JDBCController.class);

    /**
     * 根据请求参数，判断新增、修改等调用方式
     */
    @PostMapping
    public ApiResult operateSql(@RequestBody @Validated JdbcRequestForm form) {
        // 登录校验
        this.checkUserToken(getToken());

        if (JdbcTypeEnum.Insert.equals(form.getOrder())) {
            // 新增
            return this.insert(form);
        } else if (JdbcTypeEnum.Update.equals(form.getOrder())) {
            // 更新
            return this.update(form);
        } else if (JdbcTypeEnum.Delete.equals(form.getOrder())) {
            // 删除
            return this.delete(form);
        } else if (JdbcTypeEnum.Query.equals(form.getOrder())) {
            // 查询
            return this.query(form);
        } else if (JdbcTypeEnum.Count.equals(form.getOrder())) {
            // 查询总数
            return this.count(form);
        } else if (JdbcTypeEnum.Page.equals(form.getOrder())) {
            // 分页查询
            return this.page(form);
        }
        return success();
    }

    /**
     * 登录校验
     */
    private void checkUserToken(String token) {
        // 查询调度系统
//        sysTypeService.getByCode("");
        // 查看登录用户是否登录的调度系统
//        getUserSession();
    }

    /**
     * 新增
     */
    public ApiResult insert(JdbcRequestForm form) {
        if (StringUtils.isBlank(form.getData()) || JSONObject.parseObject(form.getData()).isEmpty()) {
            throw new AppException("字段参数不能为空", RequestCode.Form_Validate_Error);
        }

        // 将数据组装成sql
        String sql = this.initInsertSql(form);
        try {
            Connection connection = this.getConnection();
            PreparedStatement pst = connection.prepareStatement(sql);
            int execute = pst.executeUpdate();
            logger.info("执行语句：" + sql + "," + execute + "行数据受影响");

            // 查询主键
            pst = connection.prepareStatement("SELECT IDENT_CURRENT('"+ form.getEntity() +"')  AS id");
            ResultSet resultSet = pst.executeQuery();
            logger.info("执行语句：" + sql + "," + resultSet + "结果集");
            List<Map<String, Object>> returnResultToList = returnResultToList(resultSet);
            this.close(null, pst, connection);
            return success(returnResultToList.get(0).get("id"));
        } catch (SQLException e) {
            logger.error("异常提醒", e);
        }
        return error(0);
    }

    /**
     * 修改
     */
    public ApiResult update(JdbcRequestForm form) {
        if (StringUtils.isBlank(form.getPrimaryKey())) {
            throw new AppException("主键不能为空", RequestCode.Form_Validate_Error);
        }
        if (StringUtils.isBlank(form.getPrimaryKeyValue())) {
            throw new AppException("主键值不能为空", RequestCode.Form_Validate_Error);
        }
        if (StringUtils.isBlank(form.getData()) || JSONObject.parseObject(form.getData()).isEmpty()) {
            throw new AppException("字段参数不能为空", RequestCode.Form_Validate_Error);
        }

        String sql = this.initUpdateSql(form);
        try {
            Connection connection = this.getConnection();
            PreparedStatement pst = connection.prepareStatement(sql);
            int execute = pst.executeUpdate();
            logger.info("执行语句：" + sql + "," + execute + "行数据受影响");
            this.close(null, pst, connection);
            return success(execute);
        } catch (SQLException e) {
            logger.error("异常提醒", e);
        }
        return error(0);
    }

    /**
     * 删除
     */
    public ApiResult delete(JdbcRequestForm form) {
        if (form.getPrimaryKey() == null) {
            throw new AppException("主键不能为空", RequestCode.Form_Validate_Error);
        }
        if (StringUtils.isBlank(form.getPrimaryKeyValue())) {
            throw new AppException("主键值不能为空", RequestCode.Form_Validate_Error);
        }

        String sql = this.initDeleteSql(form);
        try {
            Connection connection = this.getConnection();
            PreparedStatement pst = connection.prepareStatement(sql);
            int execute = pst.executeUpdate();
            logger.info("执行语句：" + sql + "," + execute + "行数据受影响");
            this.close(null, pst, connection);
            return success(execute);
        } catch (SQLException e) {
            logger.error("异常提醒", e);
        }
        return error(0);
    }

    /**
     * 查询，返回结果集  
     */
    public ApiResult query(JdbcRequestForm form) {
        String sql = this.initQuerySql(form);
        List<Map<String, Object>> returnResultToList = null;
        try {
            Connection connection = this.getConnection();
            PreparedStatement pst = connection.prepareStatement(sql);
            ResultSet resultSet = pst.executeQuery();
            logger.info("执行语句：" + sql + "," + resultSet + "结果集");
            System.out.println("这是"+sql);
            System.out.println("小king"+logger.getName());
            returnResultToList = returnResultToList(resultSet);
            this.close(resultSet, pst, connection);
            return success(returnResultToList);
        } catch (SQLException e) {
            logger.error("异常提醒", e);
        }
        return error(0);
    }

    /**
     * 分页查询
     */
    public ApiResult page(JdbcRequestForm form) {
        String querySql = this.initQuerySql(form);
        String countSql = this.initCountSql(form);

        List<Map<String, Object>> returnResultToList = null;

        try {
            // 查询集合
            Connection connection = this.getConnection();
            PreparedStatement pst = connection.prepareStatement(querySql);
            ResultSet resultSet = pst.executeQuery();
            logger.info("分页查询集合，执行语句：" + querySql + "," + resultSet + "结果集");
            System.out.println("查询的sql是"+querySql);
            returnResultToList = returnResultToList(resultSet);

            // 查询总数
            pst = connection.prepareStatement(countSql);
            resultSet = pst.executeQuery();
            logger.info("分页查询总数，执行语句：" + countSql + "," + resultSet + "结果集");
            Integer total = (Integer) returnResultToList(resultSet).get(0).get("total");

            this.close(resultSet, pst, connection);
            // 获取结果集的total字段，total字段名是固定的，不可修改
            return success(new PageBean<>(returnResultToList, form.getPageNum(), form.getPageSize(), total));
        } catch (SQLException e) {
            logger.error("异常提醒", e);
        }
       return error(0);
    }

    /**
     * 查询总数
     */
    public ApiResult count(JdbcRequestForm form) {
        String sql = this.initCountSql(form);
        List<Map<String, Object>> returnResultToList = null;
        try {
            Connection connection = this.getConnection();
            PreparedStatement pst = connection.prepareStatement(sql);
            ResultSet resultSet = pst.executeQuery();
            logger.info("执行语句：" + sql + "," + resultSet + "结果集");
            returnResultToList = returnResultToList(resultSet);
            this.close(resultSet, pst, connection);
            // 获取结果集的total字段，total字段名是固定的，不可修改
            return success(returnResultToList.get(0).get("total"));
        } catch (SQLException e) {
            logger.error("异常提醒", e);
        }
        return error(0);
    }

    /**
     * 数据返回集合
     */
    public List<Map<String, Object>> returnResultToList(ResultSet resultSet) {
        List<Map<String, Object>> values = null;
        try {
            // 键: 存放列的别名, 值: 存放列的值.
            values = new ArrayList<>();
            // 存放字段名
            List<String> columnName = new ArrayList<>();
            ResultSetMetaData rsmd = resultSet.getMetaData();
            for (int i = 0; i < rsmd.getColumnCount(); i++) {
                // 字段名
                columnName.add(rsmd.getColumnLabel(i + 1));
            }
            Map<String, Object> map = null;
            // 处理 ResultSet, 使用 while 循环
            while (resultSet.next()) {
                map = new HashMap<>();
                for (String column : columnName) {
                    Object value = resultSet.getObject(column);
                    map.put(column, value);
                    System.out.print(value + "\t");
                }
                // 把一条记录的 Map 对象放入准备的 List 中
                values.add(map);
            }
        } catch (SQLException e) {
            logger.error("jdbc结果集转换失败", e);
        }
        return values;
    }

    /**
     * 获取链接
     */
    public Connection getConnection() throws SQLException {
        Connection connection = null;
        try {
            Class.forName(driver);
            connection = DriverManager.getConnection(url, username, password);
        } catch (ClassNotFoundException e) {
            logger.error("jdbc获取连接失败", e);

        }
        return connection;
    }

    /**
     *  * 关闭资源
     *  * @param resultSet 查询返回的结果集，没有为空
     *  * @param statement   
     *  * @param connection
     */
    private void close(ResultSet resultSet, Statement statement, Connection connection) {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

    /**
     * 将form组装成新增的sql
     */
    private String initInsertSql(JdbcRequestForm form) {
        String sql = "insert into "
                + form.getEntity();

        String columns = "(";
        String values = "(";
        JSONObject obj = JSONObject.parseObject(form.getData());
        Set<Map.Entry<String, Object>> set = obj.entrySet();
        Iterator<Map.Entry<String, Object>> iterator = set.iterator();
        while (iterator.hasNext()) {
            Map.Entry<String, Object> entry = iterator.next();
            columns = columns + entry.getKey() + ",";
            values = values + "'" + entry.getValue() + "'" + ",";
        }

        columns = columns.substring(0, columns.length()-1) + ")";
        values = values.substring(0, values.length() - 1) + ")";

        sql = sql + columns + "values" + values + "SELECT IDENT_CURRENT('" + form.getEntity() + "')  AS id ";

        return sql;
    }

    /**
     * 将form组装成修改的sql
     */
    private String initUpdateSql(JdbcRequestForm form) {
        String sql = "update " + form.getEntity();
        String columns = " set ";
        JSONObject obj = JSONObject.parseObject(form.getData());
        Set<Map.Entry<String, Object>> set = obj.entrySet();
        Iterator<Map.Entry<String, Object>> iterator = set.iterator();
        while (iterator.hasNext()) {
            Map.Entry<String, Object> entry = iterator.next();
            columns = columns + entry.getKey() + "='" + entry.getValue() + "',";
        }

        columns = columns.substring(0, columns.length()-1)
                + " where " + form.getPrimaryKey() + "=" + form.getPrimaryKeyValue();

        sql = sql + columns;
        System.out.println("查询的sql"+sql);
        return sql;
    }

    /**
     * 将form组装成删除的sql
     */
    private String initDeleteSql(JdbcRequestForm form) {
        String sql = "delete from " + form.getEntity() + " where "
                + form.getPrimaryKey() + " = " + form.getPrimaryKeyValue();
        return sql;
    }

    /**
     * 将form组装成修改的sql
     */
    private String initQuerySql(JdbcRequestForm form) {
        String sql = "select * from " + form.getEntity() + " where del_flag = 'N' ";
        if (StringUtils.isNotBlank(form.getData())) {
            sql = sql + " and " + form.getData();
        }
        if (form.getPageNum() != null && form.getPageSize() != null) {
            int offset = (form.getPageNum() - 1)*form.getPageSize();
            sql = sql + " ORDER BY row_number () OVER (ORDER BY(SELECT 1)) offset "
                    + offset +" ROWS FETCH NEXT "+ form.getPageSize() +" ROWS ONLY";
        }
        return sql;
    }

    /**
     * 将form组装成查询总数的sql
     */
    private String initCountSql(JdbcRequestForm form) {
        String sql = "select count(*) as total from " + form.getEntity() + " where del_flag = 'N' ";
        if (StringUtils.isNotBlank(form.getData())) {
            sql = sql + " and " + form.getData();
        }
        return sql;
    }
}
